package dbmanager

import (
	"reflect"
	"github.com/astaxie/beego/orm"
	"fmt"
	"time"
	"cloud_worm/util/umodels/mysqldb"
	"cloud_worm/util/uconst"
)

const (
	AgentOnline  = 0               // 在线
	AgentOffline = 1               // 下线
	AgentInvalid = 100             // 无效的

	AgentOnlineVersion = 200000000
	AgentPreVersion    = 100000000
)

type WormId struct {
	Id 			int64
	AgentId 	string

	IP			string
	Platform 	string
	Version 	int
	ServerAddr 	string

	Created 	int64
	Updated 	int64
	Status 		int
}

func (w *WormId) TableName() string {
	return reflect.TypeOf(*w).Name()
}

func (w *WormId) Read(fields ...string) error {
	err := orm.NewOrm().Read(w, fields...)
	if err != nil {
		return fmt.Errorf("%s, %s", w.TableName(), err.Error())
	}
	return nil
}

func (w *WormId) Update(fields ...string) error {
	_, err := orm.NewOrm().Update(w, fields...)
	if err != nil {
		return fmt.Errorf("%s, %s", w.TableName(), err.Error())
	}
	return nil
}

func (w *WormId) Insert() error {
	_, err := orm.NewOrm().Insert(w)
	if err != nil {
		return fmt.Errorf("%s, %s", w.TableName(), err.Error())
	}

	return nil
}

func init()  {
	orm.RegisterModel(new(WormId))
}

func (w *WormId) Count(status int, version int, wormId string, ip string, platform string,
	serverAddr string, startTime int, endTime int, runMode string) (int64, error) {

	qs := orm.NewOrm().QueryTable(w)
	if status != mysqldb.StatusAll {
		qs = qs.Filter("status", status)
	} else {
		qs = qs.Exclude("status", AgentInvalid)            // 全部的话需要排除无效值100
	}

	// 注意：区分预发（测试）分支、线上分支
	//      1. 预发分支，version版本号： 1开头，其中预发分支，走else分支即可
	//      2. 线上分支，version版本号： 2开头
	if version != mysqldb.SelectIntAll {
		qs = qs.Filter("version", version)
	} else if runMode == uconst.RunModePub {
		qs = qs.Filter("version__gt", AgentOnlineVersion)                                                 // 线上
	} else {
		qs = qs.Filter("version__lt", AgentOnlineVersion).Filter("version__gt", AgentPreVersion)        // 预发
	}

	if wormId != mysqldb.SelectStrAll {
		qs = qs.Filter("worm_id__istartswith", wormId)
	}
	if ip != mysqldb.SelectStrAll {
		qs = qs.Filter("ip__istartswith", ip)
	}
	if platform != mysqldb.SelectStrAll {
		qs = qs.Filter("platform__istartswith", platform)
	}
	if serverAddr != mysqldb.SelectStrAll {
		qs = qs.Filter("server_addr__istartswith", serverAddr)
	}

	// 时间条件
	if startTime != mysqldb.SelectIntAll {
		qs = qs.Filter("updated__gte", startTime)
	}
	if endTime != mysqldb.SelectIntAll {
		qs = qs.Filter("updated__lte", endTime)
	}

	count, err := qs.Count()
	if err != nil {
		return 0, err
	}

	return count, nil
}

func (w *WormId) Select(offset int, limit int,
	status int, version int, wormId string, ip string,
	platform string, serverAddr string, startTime int, endTime int, runMode string) ([]*WormId, error) {

	var wolIds []*WormId

	qs := orm.NewOrm().QueryTable(w).Offset(offset).Limit(limit).OrderBy("-updated")
	if status != mysqldb.StatusAll {
		qs = qs.Filter("status", status)
	} else {
		qs = qs.Exclude("status", AgentInvalid)            // 全部的话需要排除无效值100
	}
	// 注意：区分预发（测试）分支、线上分支
	//      1. 预发分支，version版本号： 1开头，其中预发分支，走else分支即可
	//      2. 线上分支，version版本号： 2开头
	if version != mysqldb.SelectIntAll {
		qs = qs.Filter("version", version)
	} else if runMode == uconst.RunModePub {
		qs = qs.Filter("version__gt", AgentOnlineVersion)                                                 // 线上
	} else {
		qs = qs.Filter("version__lt", AgentOnlineVersion).Filter("version__gt", AgentPreVersion)        // 预发
	}
	if wormId != mysqldb.SelectStrAll {
		qs = qs.Filter("agent_id__istartswith", wormId)
	}
	if ip != mysqldb.SelectStrAll {
		qs = qs.Filter("ip__istartswith", ip)
	}
	if platform != mysqldb.SelectStrAll {
		qs = qs.Filter("platform__istartswith", platform)
	}
	if serverAddr != mysqldb.SelectStrAll {
		qs = qs.Filter("server_addr__istartswith", serverAddr)
	}

	// 时间条件
	if startTime != mysqldb.SelectIntAll {
		qs = qs.Filter("updated__gte", startTime)
	}
	if endTime != mysqldb.SelectIntAll {
		qs = qs.Filter("updated__lte", endTime)
	}

	_, err := qs.All(&wolIds)
	if err != nil {
		return nil, err
	}

	return wolIds, nil
}

func (w *WormId) SelectValidByIP(ip string) error {
	var wolIds []*WormId

	// 基于ip查找，有效的agent对象（offline和online）
	// access server中保证了，（ip--id--valid）只有一条记录
	qs := orm.NewOrm().QueryTable(w).Filter("ip", ip).Filter("status__in", AgentOffline, AgentOnline)
	_, err := qs.All(&wolIds)
	if err != nil {
		return err
	}

	if len(wolIds) <= 0 {
		return fmt.Errorf("no result for %s", ip)
	}

	if len(wolIds) > 1 {
		return fmt.Errorf("select result count: %d, ", len(wolIds))
	}

	w.Id = wolIds[0].Id
	w.AgentId = wolIds[0].AgentId
	w.IP = wolIds[0].IP
	w.Platform = wolIds[0].Platform
	w.Version = wolIds[0].Version
	w.ServerAddr = wolIds[0].ServerAddr
	w.Created = wolIds[0].Created
	w.Updated = wolIds[0].Updated
	w.Status = wolIds[0].Status
	return nil
}

func (w *WormId) SetInvalidAgentId(ip string, id string) error {
	o := orm.NewOrm()
	_, err := o.Raw("UPDATE WormId SET status=?, updated=? WHERE ip=? and agent_id!=?", AgentInvalid, time.Now().Unix(), ip, id).Exec()
	if err != nil {
		return err
	}

	//num, _ := res.RowsAffected()
	return nil
}

func (w *WormId) SetAgentOffline(wormId string, version int) error {
	o := orm.NewOrm()
	_, err := o.Raw("UPDATE WormId set status=?, updated=? WHERE agent_id=? and version=?", AgentOffline, time.Now().Unix(), wormId, version).Exec()
	if err != nil {
		return err
	}

	return nil
}

func (w *WormId) SetAgentOnline(ip string, version int, platform string, serverAddr string, wormId string) error {
	o := orm.NewOrm()
	_, err := o.Raw("UPDATE WormId set status=?, ip=?, version=?, server_addr=?, updated=?, platform=? WHERE worm_id=?",
		AgentOnline, ip, version, serverAddr, time.Now().Unix(), platform, wormId).Exec()
	if err != nil {
		return err
	}
	return nil
}

func (w *WormId) SelectMaxId() (int64, error) {
	var MaxId int64
	o := orm.NewOrm()
	err := o.Raw("SELECT MAX(id) as max_id FROM WormId").QueryRow(&MaxId)
	if err != nil {
		return MaxId, err
	}

	return MaxId, nil
}

type AccessSrvCount struct {
	ServerAddr 		string		`json:"server_addr"`
	Count 			int			`json:"count"`
}

func (w *WormId) SelectAccessSrvList(status int) ([]*AccessSrvCount, error) {
	qb, err := orm.NewQueryBuilder(mysqldb.DBDriver)
	if err != nil {
		return nil, err
	}

	serverAddr := fmt.Sprintf("%s.%s", w.TableName(), "server_addr")
	qb = qb.Select(serverAddr, "count(1) as count").From(w.TableName())
	if status != mysqldb.SelectIntAll {
		qb = qb.Where(fmt.Sprintf("status=%d", status))
	} else {
		// 无效机器不考虑
		qb = qb.Where(fmt.Sprintf("status!=%d", AgentInvalid))
	}
	qb = qb.GroupBy("server_addr")

	sql := qb.String()
	var servers []*AccessSrvCount
	_, err = orm.NewOrm().Raw(sql).QueryRows(&servers)
	if err != nil {
		return nil, err
	}

	return servers, nil
}

type AgentVersionCnt struct {
	Version 		int			`json:"version"`
	Count 			int			`json:"count"`
}

func (w *WormId) SelectVersionList(status int) ([]*AgentVersionCnt, error) {
	qb, err := orm.NewQueryBuilder(mysqldb.DBDriver)
	if err != nil {
		return nil, err
	}

	version := fmt.Sprintf("%s.%s", w.TableName(), "version")
	qb = qb.Select(version, "count(1) as count").From(w.TableName())
	if status != mysqldb.SelectIntAll {
		qb = qb.Where(fmt.Sprintf("status=%d", status))
	} else {
		// 无效机器不考虑
		qb = qb.Where(fmt.Sprintf("status!=%d", AgentInvalid))
	}
	qb = qb.GroupBy("version")

	sql := qb.String()
	var versions []*AgentVersionCnt
	_, err = orm.NewOrm().Raw(sql).QueryRows(&versions)
	if err != nil {
		return nil, err
	}

	return versions, nil
}

type MachinePlatformCnt struct {
	Platform 		string			`json:"platform"`
	Count 			int 			`json:"count"`
}

func (w *WormId) SelectPlatformList(status int) ([]*MachinePlatformCnt, error) {
	qb, err := orm.NewQueryBuilder(mysqldb.DBDriver)
	if err != nil {
		return nil, err
	}

	version := fmt.Sprintf("%s.%s", w.TableName(), "platform")
	qb = qb.Select(version, "count(1) as count").From(w.TableName())
	if status != mysqldb.SelectIntAll {
		qb = qb.Where(fmt.Sprintf("status=%d", status))
	} else {
		// 无效机器不考虑
		qb = qb.Where(fmt.Sprintf("status!=%d", AgentInvalid))
	}
	qb = qb.GroupBy("platform")

	sql := qb.String()
	var platforms []*MachinePlatformCnt
	_, err = orm.NewOrm().Raw(sql).QueryRows(&platforms)
	if err != nil {
		return nil, err
	}

	return platforms, nil
}

func (w *WormId) SelectStatusVerOver(offset int, limit int, status int, version int, runMode string) ([]*WormId, error) {
	var wolIds []*WormId

	qs := orm.NewOrm().QueryTable(w).Offset(offset).Limit(limit)
	if status != mysqldb.StatusAll {
		qs = qs.Filter("status", status)
	} else {
		qs = qs.Exclude("status", AgentInvalid)            // 全部的话需要排除无效值100
	}
	// 注意：区分预发（测试）分支、线上分支
	//      1. 预发分支，version版本号： 1开头，其中预发分支，走else分支即可
	//      2. 线上分支，version版本号： 2开头
	if version != mysqldb.SelectIntAll {
		qs = qs.Filter("version", version)
	} else if runMode == uconst.RunModePub {
		qs = qs.Filter("version__gt", AgentOnlineVersion)                                                 // 线上
	} else {
		qs = qs.Filter("version__lt", AgentOnlineVersion).Filter("version__gt", AgentPreVersion)        // 预发
	}

	_, err := qs.All(&wolIds)
	if err != nil {
		return nil, err
	}

	return wolIds, nil
}

func (w *WormId) SelectStatus(offset int, limit int, status int, runMode string) ([]*WormId, error) {
	var wolIds []*WormId

	qs := orm.NewOrm().QueryTable(w).Offset(offset).Limit(limit)
	if status != mysqldb.StatusAll {
		qs = qs.Filter("status", status)
	} else {
		qs = qs.Exclude("status", AgentInvalid)            // 全部的话需要排除无效值100
	}

	// 注意：区分预发（测试）分支、线上分支
	//      1. 预发分支，version版本号： 1开头，其中预发分支，走else分支即可
	//      2. 线上分支，version版本号： 2开头
	if runMode == uconst.RunModePub {
		qs = qs.Filter("version__gt", AgentOnlineVersion)                                                 // 线上
	} else {
		qs = qs.Filter("version__lt", AgentOnlineVersion).Filter("version__gt", AgentPreVersion)        // 预发
	}

	_, err := qs.All(&wolIds)
	if err != nil {
		return nil, err
	}

	return wolIds, nil
}

func (w *WormId) CountStatus(status int, runMode string) (int64, error) {

	qs := orm.NewOrm().QueryTable(w)
	if status != mysqldb.StatusAll {
		qs = qs.Filter("status", status)
	}
	// 注意：区分预发（测试）分支、线上分支
	//      1. 预发分支，version版本号： 1开头，其中预发分支，走else分支即可
	//      2. 线上分支，version版本号： 2开头
	if runMode == uconst.RunModePub {
		qs = qs.Filter("version__gt", AgentOnlineVersion)                                                 // 线上
	} else {
		qs = qs.Filter("version__lt", AgentOnlineVersion).Filter("version__gt", AgentPreVersion)        // 预发
	}

	count, err := qs.Count()
	if err != nil {
		return 0, err
	}

	return count, nil
}

// 基于IP列表查询; ips的list为空时，表示不限定ip范围，取全量
func (w *WormId) CountByIPs(offset int, limit int, status int, version int, ips []string, runMode string) (int64, error) {

	qs := orm.NewOrm().QueryTable(w).Offset(offset).Limit(limit)
	if status != mysqldb.StatusAll {
		qs = qs.Filter("status", status)
	} else {
		qs = qs.Exclude("status", AgentInvalid)            // 全部的话需要排除无效值100
	}
	// 注意：区分预发（测试）分支、线上分支
	//      1. 预发分支，version版本号： 1开头，其中预发分支，走else分支即可
	//      2. 线上分支，version版本号： 2开头
	if version != mysqldb.SelectIntAll {
		qs = qs.Filter("version", version)
	} else if runMode == uconst.RunModePub {
		qs = qs.Filter("version__gt", AgentOnlineVersion)                                                 // 线上
	} else {
		qs = qs.Filter("version__lt", AgentOnlineVersion).Filter("version__gt", AgentPreVersion)        // 预发
	}

	if len(ips) != 0 {
		qs = qs.Filter("ip__in", ips)
	}

	cnt, err := qs.Count()
	if err != nil {
		return 0, err
	}

	return cnt, nil
}

// 基于IP列表查询, ips的list为空时，表示不限定ip范围，取全量
func (w *WormId) SelectByIPs(offset int, limit int, status int, version int, ips []string, runMode string) ([]*WormId, error) {
	var wolIds []*WormId

	qs := orm.NewOrm().QueryTable(w).Offset(offset).Limit(limit)
	if status != mysqldb.StatusAll {
		qs = qs.Filter("status", status)
	} else {
		qs = qs.Exclude("status", AgentInvalid)            // 全部的话需要排除无效值100
	}
	// 注意：区分预发（测试）分支、线上分支
	//      1. 预发分支，version版本号： 1开头，其中预发分支，走else分支即可
	//      2. 线上分支，version版本号： 2开头
	if version != mysqldb.SelectIntAll {
		qs = qs.Filter("version", version)
	} else if runMode == uconst.RunModePub {
		qs = qs.Filter("version__gt", AgentOnlineVersion)                                                 // 线上
	} else {
		qs = qs.Filter("version__lt", AgentOnlineVersion).Filter("version__gt", AgentPreVersion)        // 预发
	}

	if len(ips) != 0 {
		qs = qs.Filter("ip__in", ips)
	}

	_, err := qs.All(&wolIds)
	if err != nil {
		return nil, err
	}

	return wolIds, nil
}

type WormConnectionInfo struct {
	WormId
	ApplicationName         string
	ApplicationID           int
}

// 获取连接的详情
func (w *WormId) GetConnectionInfo(offset int, limit int, status int, version int,
	wormId string, ip string, platform string, serverAddr string,
	startTime int, endTime int, runMode string) ([]*WormConnectionInfo, error) {
	// 按照条件查询 worm id 的信息
	wolIDs, err := w.Select(offset, limit, status, version, wormId, ip, platform, serverAddr, startTime, endTime, runMode)
	if err != nil {
		return nil, err
	}

	// 查询ip到application id的对应关系
	ipToAppID, appIDs, err := w.getAppIDMap(wolIDs)
	if err != nil {
		return nil, err
	}

	// 查指定application id的application name
	appIDToName, err := w.getAppName(appIDs)
	if err != nil {
		return nil, err
	}

	var consInfo []*WormConnectionInfo
	for _, item := range wolIDs {
		id := ipToAppID[item.IP]
		name := appIDToName[id]
		one := &WormConnectionInfo{
			WormId: *item,
			ApplicationID: id,
			ApplicationName: name,
		}

		consInfo = append(consInfo, one)
	}

	return consInfo, nil
}

// 得到ip到application id的映射关系
func (w *WormId) getAppIDMap(wolIDs []*WormId) (map[string]int, []int, error) {
	qb, err := orm.NewQueryBuilder("mysql")
	if err != nil {
		return nil, nil, err
	}

	m := &Machine{}
	appIDField := fmt.Sprintf("%s.%s", m.TableName(), "apply_id")
	ipField := fmt.Sprintf("%s.%s", m.TableName(), "ip")
	qb = qb.Select(appIDField, ipField).From(m.TableName()).Where(mysqldb.SelectCondFalse)
	for _, item := range wolIDs {
		qb = qb.Or(fmt.Sprintf("ip='%s'", item.IP))
	}
	sql := qb.String()

	type AppIPMap struct {
		ApplyId         int
		Ip              string
	}
	var appipMaps []*AppIPMap
	_, err = orm.NewOrm().Raw(sql).QueryRows(&appipMaps)
	if err != nil {
		return nil, nil, err
	}

	var appIDs []int
	ipToAppID := make(map[string]int)
	for _, item := range appipMaps {
		_, exist := ipToAppID[item.Ip]
		if exist != true {
			ipToAppID[item.Ip] = item.ApplyId
		}
		appIDs = append(appIDs, item.ApplyId)
	}

	return ipToAppID, appIDs, nil
}

// 得到application id对应的application name值
func (w *WormId) getAppName(appIDs []int) (map[int]string, error) {
	qb, err := orm.NewQueryBuilder("mysql")
	if err != nil {
		return nil, err
	}

	a := Application{}
	idField := fmt.Sprintf("%s.%s", a.TableName(), "id")
	nameField := fmt.Sprintf("%s.%s", a.TableName(), "name")
	qb = qb.Select(idField, nameField).From(a.TableName()).Where(mysqldb.SelectCondFalse)
	for _, id := range appIDs {
		qb = qb.Or(fmt.Sprintf("id=%d", id))
	}
	sql := qb.String()

	type ApplyBriefInfo struct {
		Id          int
		Name        string
	}
	var apps []*ApplyBriefInfo
	_, err = orm.NewOrm().Raw(sql).QueryRows(&apps)
	if err != nil {
		return nil, err
	}

	idToName := make(map[int]string)
	for _, item := range apps {
		_, exist := idToName[item.Id]
		if exist == false {
			idToName[item.Id] = item.Name
		}
	}

	return idToName, nil
}